/*
select * from MSDB.dbo.sysjobactivity

Select * from msdb.dbo.sysjobhistory

Select * from msdb.dbo.sysjobstepslogs

select Max(instance_id), job_id
from msdb.dbo.sysjobhistory
Group by job_id




Select * from 
msdb.dbo.sysjobhistory sjh
Join
(select Max(instance_id) as Instance_id_Max, job_id
from msdb.dbo.sysjobhistory
Group by job_id) sjhMax on sjhMax.Instance_id_Max = sjh.instance_id 


select Max(instance_id) as Instance_id_Max,
 job_id, 
 step_id
from msdb.dbo.sysjobhistory
Group by job_id, step_id
Order by job_id

--Select 
--Left(Cast(sjh.run_Date as Char(8)), 4)+'-'+Substring(Cast(sjh.run_Date as Char(8)),5,2)+'-'+Right(Cast(sjh.run_Date as Char(8)),2) as RunDate,
--Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)) as RunTime1,
--Left(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) + ':' +
--Substring(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),3,2) + ':' +
--Right(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) as RunTime2,

--Left(Cast(sjh.run_Date as Char(8)), 4)+'-'+Substring(Cast(sjh.run_Date as Char(8)),5,2)+'-'+Right(Cast(sjh.run_Date as Char(8)),2) + 'T' +
--Left(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) + ':' +
--Substring(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),3,2) + ':' +
--Right(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) as RunTime3,

--Left(Cast(sjh.run_Date as Char(8)), 4)+'-'+Substring(Cast(sjh.run_Date as Char(8)),5,2)+'-'+Right(Cast(sjh.run_Date as Char(8)),2) + 'T' +
--Left(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) + ':' +
--Substring(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),3,2) + ':' +
--Right(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) + '.000' as RunTime4,

--Convert(DateTime,
--Left(Cast(sjh.run_Date as Char(8)), 4)+'-'+Substring(Cast(sjh.run_Date as Char(8)),5,2)+'-'+Right(Cast(sjh.run_Date as Char(8)),2) + 'T' +
--Left(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) + ':' +
--Substring(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),3,2) + ':' +
--Right(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) + '.000') as RunTime5,

--Retrieve the last run status of each job step
Select
Cast (Left(Cast(sjh.run_Date as Char(8)), 4)+'-'+Substring(Cast(sjh.run_Date as Char(8)),5,2)+'-'+Right(Cast(sjh.run_Date as Char(8)),2) + 'T' +
Left(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) + ':' +
Substring(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),3,2) + ':' +
Right(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) + '.000' as DateTime) as RunTime6,

sjh.* from 
msdb.dbo.sysjobhistory sjh
Join
(select Max(instance_id) as Instance_id_Max,
 job_id, 
 step_id
from msdb.dbo.sysjobhistory
Group by job_id, step_id) sjhMax on sjhMax.Instance_id_Max = sjh.instance_id
Order by sjh.job_id, sjh.step_id asc
*/

Declare
@StartTime Datetime,
@Jobname	Varchar(300),
@JobID		Char(36)

--test variables
Set @StartTime = DATEADD(HH,-2400,Getdate())
Set @JobID = '4BA7E04B-141D-4702-B92D-D222FEDB8336'
--Set @Jobname = 'VDBA - Failed Jobs'

--Start of Proc

Declare 
@JobStartDate		Int,
@JobStartTime		Int,
@JobStartInstance	Int





--Determine how far back to retrieve failed job info.
--Restrict the the most recent @StartTime or sucsesfull run of job defined by @Jobname or @JobID	

--Determine @JobID

If @JobID is null --@job_ID needs to be defined, try to find it based on @job_name
	Select @JobID = Job_id 
	from msdb.dbo.sysjobs 
	where name = @Jobname

If @JobID is null --IF @Job_id is still null then preveos select statement returned Null because @Job_name was not a valid name or null.
	Set @JobStartInstance = 0 
Else --Find the Instance number from the last sucsesfull run of the job
Select @JobStartInstance = Max(sjh.instance_id )
	From msdb.dbo.sysjobhistory sjh
	Where sjh.job_id = @JobID and sjh.run_status = 1 


--If @JobID is Null and @Jobname is null 
--	Set @JobStartInstance = 0
--Else
--	Begin
--	If @JobID is not 
--	End









--Convert StartTime to 2 interger values YYYYMMDD and HHMMSS
--Select Cast(Convert(Char(8),@StartTime,112) as int) as date, Cast(Replace(Convert(VarChar(8),@StartTime,108),':','') as int) as time

If @StartTime is not null
Begin
	Set @JobStartDate = Cast(Convert(Char(8),@StartTime,112) as int)
	Set @JobStartTime = Cast(Replace(Convert(VarChar(8),@StartTime,108),':','') as int)
End
Else
Begin
	Set @JobStartDate = 0
	Set @JobStartTime = 0
End

--tshooting
Select @StartTime as 'Start time and date',
 @JobStartDate as 'run Date as int',
 @JobStartTime as 'run time as int',
 @Jobname as 'Job name',
 @JobID as 'Job ID',
 @JobStartInstance as 'Job start instance'

--select * from msdb.dbo.sysjobs

--Retrieve the last run status of each job step
Select
Cast (Left(Cast(sjh.run_Date as Char(8)), 4)+'-'+Substring(Cast(sjh.run_Date as Char(8)),5,2)+'-'+Right(Cast(sjh.run_Date as Char(8)),2) + 'T' +
Left(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) + ':' +
Substring(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),3,2) + ':' +
Right(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) + '.000' as DateTime) as Run_DateTime,

sjh.instance_id,
sjh.job_id,
sj.name as Job_name,
--sjh.step_id,
sjh.step_name,
sjh.run_status,
sjh.message,
sjh.run_duration,

sjh.*
from msdb.dbo.sysjobhistory sjh
Join
(select Max(instance_id) as Instance_id_Max,
 job_id, 
 step_id
from msdb.dbo.sysjobhistory
Group by job_id, step_id) sjhMax on sjhMax.Instance_id_Max = sjh.instance_id
Join msdb.dbo.sysjobs sj on sj.job_id = sjh.job_id
Where 
 sjh.step_id = 0
 and sjh.instance_id >= @JobStartInstance 
 and (sjh.run_date >= @JobStartDate or (sjh.run_date = @JobStartDate and sjh.run_time >= @JobStartTime))
Order by Run_DateTime
--Order by sjh.job_id, sjh.step_id asc


--Select convert(DateTime,'2014-09-30T14:20:40')